package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.rdb.api.ProductCatalogRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.ProductCatalog;
import com.ndp.fb.rdb.model.ProductWeFeedCatalog;
import com.ndp.fb.rdb.model.product.FeedWeWithMateriel;
import com.ndp.fb.rdb.model.product.ProductCatalogFbFeed;
import com.ndp.fb.rdb.model.product.ProductCatalogStatusCheck;
import com.ndp.fb.rdb.model.product.ProductCatalogStatusCheckResult;
import com.ndp.fb.util.SQLUtil;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Evan on 2016/4/19.
 */
@Service
public class ProductCatalogRdbServiceImpl extends DaoTemplate implements ProductCatalogRdbService {


    @Override
    public Long saveWeFeedCatalog(ProductWeFeedCatalog productWeFeedCatalog) {
        return saveResultId(productWeFeedCatalog);
    }

    @Override
    public Long saveCatalog(ProductCatalog productCatalog) {
        return saveResultId(productCatalog);
    }


    @Override
    public void updateStatus(Long id, String status) {
        String sql  = "update product_catalog set operate_status = ? where product_catalog_id = ? ";
        getJdbcTemplate().update(sql,new Object[]{status, id});
    }

    @Override
    public void updateNoproduct(Long productCatalogId, String status) {
        String sql  = "update product_catalog set operate_status = ? where product_catalog_id = ? and operate_status = 'no_product' ";
        getJdbcTemplate().update(sql,new Object[]{status, productCatalogId});
    }

    @Override
    public void updateTemplateMaterielId(Long productCatalogId, Long ecTemplateMaterielId) {
        String sql  = "update product_catalog set ec_template_materiel_id = ? where product_catalog_id = ? ";
        getJdbcTemplate().update(sql,new Object[]{ecTemplateMaterielId, productCatalogId});
    }

    @Override
    public void updateStatusByWeFeedId(Long weFeedId, String status) {
        String sql  = "update product_catalog set operate_status = ? where product_catalog_id in (select a.product_catalog_id from product_we_feed_catalog a where a.we_feed_id = ?)";
        getJdbcTemplate().update(sql,new Object[]{status, weFeedId});
    }

    @Override
    public List<ProductCatalogStatusCheck> findHasErrorProductCnt(Long weFeedId) {
        String sql = "SELECT t.product_catalog_id, SUM(t.total_count) AS total_count FROM product_catalog_status_check t WHERE t.result = 'fatal' AND t.product_catalog_id IN (SELECT a.product_catalog_id FROM product_we_feed_catalog a WHERE a.we_feed_id=?) GROUP BY t.product_catalog_id";
        return getJdbcTemplate().query(sql, new Object[]{weFeedId}, DefaultRowMapper.autoRowMapper(ProductCatalogStatusCheck.class));
    }

    @Override
    public List<ProductCatalogStatusCheckResult> findOverview(Long weFeedId) {
        String sql = "SELECT\n" +
                "\tt.*, foo.product_catalog_name, foo.latest_upload_end_time\n" +
                "FROM\n" +
                "\tproduct_catalog_status_check t,\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tb.product_catalog_id,\n" +
                "\t\t\tc.product_catalog_name,\n" +
                "      d.latest_upload_end_time\n" +
                "\t\tFROM\n" +
                "\t\t\tproduct_we_feed a,\n" +
                "\t\t\tproduct_we_feed_catalog b,\n" +
                "\t\t\tproduct_catalog c,\n" +
                "      product_fb_feed d\n" +
                "\t\tWHERE\n" +
                "\t\t\ta.id = b.we_feed_id\n" +
                "\t\tAND b.product_catalog_id = c.product_catalog_id\n" +
                "    AND c.product_catalog_id = d.product_catalog_id\n" +
                "\t\tAND a.id = ?\n" +
                "\t\tAND a.is_delete = 0\n" +
                "\t) AS foo\n" +
                "WHERE t.product_catalog_id = foo.product_catalog_id";
        return getJdbcTemplate().query(sql, new Object[]{weFeedId}, DefaultRowMapper.autoRowMapper(ProductCatalogStatusCheckResult.class));
    }

    @Override
    public void batchInsertToCheck(List<ProductCatalogStatusCheck> list, Long productCatalogId) {
        String sql  = "delete from product_catalog_status_check where product_catalog_id = ? ";
        getJdbcTemplate().update(sql, new Object[]{productCatalogId});
        batchSaveResultId(list);
    }

    @Override
    public void deleteCheck(Long productCatalogId) {
        String sql  = "delete from product_catalog_status_check where product_catalog_id = ? ";
        getJdbcTemplate().update(sql, new Object[]{productCatalogId});
    }

	@Override
	public List<ProductCatalog> findProductCatalogs(Long weFeedId) {
        String sql = "SELECT b.* FROM product_catalog b, product_we_feed_catalog a WHERE b.product_catalog_id = a.product_catalog_id AND a.we_feed_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{weFeedId}, DefaultRowMapper.autoRowMapper(ProductCatalog.class));
	}

    @Override
    public List<ProductCatalogFbFeed> findProductCatalogsAndFbFeed(Long weFeedId) {
        String sql = "SELECT a.*, b.* FROM product_we_feed_catalog t, product_catalog a,product_fb_feed b WHERE t.product_catalog_id = a.product_catalog_id AND a.product_catalog_id = b.product_catalog_id AND t.we_feed_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{weFeedId}, DefaultRowMapper.autoRowMapper(ProductCatalogFbFeed.class));
    }

    @Override
    public List<FeedWeWithMateriel> findUsedAndCanUse(List<Long> weFeedIds) {
        if (!weFeedIds.isEmpty()) {
            String sql = "SELECT t.we_feed_id as id, SUM(CASE WHEN a.ec_template_materiel_id > 0 THEN 1 else 0 end) as used, COUNT(1) as canuse FROM product_we_feed_catalog t,product_catalog a WHERE t.product_catalog_id = a.product_catalog_id AND t.we_feed_id IN (" + SQLUtil.appendPlaceholder(weFeedIds.size()) + ") GROUP BY t.we_feed_id";
            return getJdbcTemplate().query(sql, weFeedIds.toArray(), DefaultRowMapper.autoRowMapper(FeedWeWithMateriel.class));
        }else {
            return new ArrayList<>();
        }
    }

    @Override
    public void updateFbFeed(Long fbFeedId, String url) {
        String sql  = "update product_fb_feed set reprocess_url = ? where product_feed_id = ? ";
        getJdbcTemplate().update(sql,new Object[]{url, fbFeedId});
    }

    @Override
    public ProductCatalog findByWeFeedIdAndEcTemplateMaterielId(Long weFeedId, Long ecTemplateMaterielId) {
        String sql = "select a.* from product_we_feed_catalog t,product_catalog a where t.product_catalog_id = a.product_catalog_id and t.we_feed_id = ? and a.ec_template_materiel_id = ?";
        return findOne(sql, new Object[]{weFeedId, ecTemplateMaterielId}, DefaultRowMapper.autoRowMapper(ProductCatalog.class));
    }

    @Override
    public ProductCatalogFbFeed findByFbCatalogId(Long catalogId) {
        String sql = "SELECT a.*, b.* FROM product_catalog a JOIN product_fb_feed b ON a.product_catalog_id = b.product_catalog_id where a.product_catalog_id = ?";
        return findOne(sql, new Object[]{catalogId}, DefaultRowMapper.autoRowMapper(ProductCatalogFbFeed.class));
    }

    @Override
    public Boolean deleteByProductCatalogIds(List<Long> catalogIds) {
        if (!catalogIds.isEmpty()) {
            String sql = "DELETE FROM product_catalog WHERE product_catalog_id IN (" + SQLUtil.appendPlaceholder(catalogIds.size()) + ")";
            String sql2 = "DELETE FROM product_we_feed_catalog WHERE product_catalog_id IN ("+ SQLUtil.appendPlaceholder(catalogIds.size()) +")";
            getJdbcTemplate().update(sql,catalogIds.toArray());
            getJdbcTemplate().update(sql2,catalogIds.toArray());
        }
        return true;
    }

    @Override
    public List<ProductCatalog> findByWeFeedIdNot(Long feedId, Long orgId) {
        String sql = "select b.* from product_we_feed a, product_we_feed_catalog t, product_catalog b where a.id=t.we_feed_id and t.product_catalog_id = b.product_catalog_id and a.org_id= ? and we_feed_id <> ?";
        return getJdbcTemplate().query(sql, new Object[]{orgId, feedId}, DefaultRowMapper.autoRowMapper(ProductCatalog.class));
    }

    @Override
    public Integer findOneUpdateFailCnt(Long catalogId) {
        String sql = "SELECT one_update_fail_cnt FROM product_catalog WHERE 1=1 AND product_catalog_id = ?";
        return getJdbcTemplate().queryForInt(sql, catalogId);
    }

    @Override
    public void updateOneUpdateBegin(Long catalogId) {
        String sql  = "UPDATE product_catalog SET one_update_fail_cnt = 0 WHERE 1=1 AND product_catalog_id = ? ";
        getJdbcTemplate().update(sql,new Object[]{catalogId});
    }

    @Override
    public void updateOneUpdatePlus(Long catalogId) {
        String sql  = "UPDATE product_catalog SET one_update_fail_cnt = one_update_fail_cnt +1 WHERE 1=1 AND product_catalog_id = ? ";
        getJdbcTemplate().update(sql,new Object[]{catalogId});
    }
}
